package com.estar.report.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.bouncycastle.jce.provider.symmetric.AES.OFB;
import org.jeecgframework.web.cgreport.common.CgReportConstant;
import org.jeecgframework.web.cgreport.exception.CgReportNotFoundException;
import org.jeecgframework.web.cgreport.service.excel.CgReportExcelServiceI;
import org.jeecgframework.web.graphreport.service.core.GraphReportServiceI;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.dao.jdbc.JdbcDao;
import org.jeecgframework.core.common.exception.BusinessException;
import org.jeecgframework.core.util.BrowserUtils;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.core.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.estar.app.business.common.service.AppService;
import com.estar.report.util.ReportQueryParamUtil;

/**
 * 
 * @Title:CgReportController
 * @description:图表配置控制器
 * @author 钟世云
 * @date 2015.4.11
 * @version V1.0
 */
@Controller
@RequestMapping("/reportController")
public class ReportController extends BaseController {
	@Autowired
	private GraphReportServiceI graphReportService;
	@Autowired
	private SystemService systemService;
	@Autowired
	private CgReportExcelServiceI cgReportExcelService;
	@Autowired
	private JdbcDao jdbcDao;
	@Autowired
	AppService appService;
	
	private static final Logger logger = Logger.getLogger(ReportController.class);
	/**
	 * 动态报表展现入口
	 * @param id 动态配置ID-code
	 * @param request
	 * @param response
	 */
	@RequestMapping(params = "list")
	public ModelAndView list(String id, HttpServletRequest request,
			HttpServletResponse response,String show,String show2,String sat,String check) {
		//step.1 根据id获取该动态报表的配置参数
		Map<String, Object>  cgReportMap = null;
		try{
			cgReportMap = graphReportService.queryCgReportConfig(id);//获取全部信息
		}catch (Exception e) {
			logger.error("系统异常：",e);
			throw new CgReportNotFoundException("动态报表配置不存在!");
		}
	
		Map mainM = (Map) cgReportMap.get(CgReportConstant.MAIN);
		List<Map<String,Object>> fieldList = (List<Map<String, Object>>) cgReportMap.get(CgReportConstant.ITEMS);
		List<Map<String,Object>> queryList = new ArrayList<Map<String,Object>>(0);//放置查询数据的list
		
		
		for(Map<String,Object> fl:fieldList){
			fl.put(CgReportConstant.ITEM_FIELDNAME, ((String)fl.get(CgReportConstant.ITEM_FIELDNAME)).toLowerCase());
			String isQuery = (String) fl.get(CgReportConstant.ITEM_ISQUERY);
			if(CgReportConstant.BOOL_TRUE.equalsIgnoreCase(isQuery)){
				loadDic(fl,fl);
				queryList.add(fl);
			}
			
		}
		request.setAttribute("show2", show2);
		request.setAttribute("check", check);//案件抽查
		request.setAttribute("sat", sat);
		request.setAttribute("show", show);//是否展示地市，高速选项
		request.setAttribute("queryList", queryList);
		request.setAttribute("config_id", id);
		request.setAttribute("filedlist", fieldList);
		request.setAttribute("mainName", mainM.get("name"));
		return new ModelAndView("gdia/report/rep");
	
	}
	
	
	/**
	 * popup入口
	 * @param id 动态配置ID-code
	 * @param request
	 * @param response
	 */
	/*@RequestMapping(params = "popup")
	public void popup(String id, HttpServletRequest request,
			HttpServletResponse response) {
		//step.1 根据id获取该动态报表的配置参数
		Map<String, Object>  cgReportMap = null;
		try{
			cgReportMap = graphReportService.queryCgReportConfig(id);
		}catch (Exception e) {
			throw new CgReportNotFoundException("动态报表配置不存在!");
		}
		//step.2 获取列表ftl模板路径
		FreemarkerHelper viewEngine = new FreemarkerHelper();
		//step.3 组合模板+数据参数，进行页面展现
		loadVars(cgReportMap);

		//step.4 页面css js引用
		cgReportMap.put(CgAutoListConstant.CONFIG_IFRAME, getHtmlHead(request));

		String html = viewEngine.parseTemplate("/org/jeecgframework/web/cgreport/engine/core/cgreportlistpopup.ftl", cgReportMap);
		PrintWriter writer = null;
		try {
			response.setContentType("text/html");
			response.setHeader("Cache-Control", "no-store");
			writer = response.getWriter();
			writer.println(html);
			writer.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				writer.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	}
	*/
	


	/**
	 * 处理数据字典
	 * @param result 查询的结果集
	 * @param beans 字段配置
	 */
	@SuppressWarnings("unchecked")
	private void dealDic(List<Map<String, Object>> result,
			List<Map<String,Object>> beans) {
		for(Map<String,Object> bean:beans){
			String dict_code = (String) bean.get(CgReportConstant.ITEM_DICCODE);
			if(StringUtil.isEmpty(dict_code)){
				//不需要处理字典
				continue;
			}else{
				List<Map<String, Object>> dicDatas = queryDicBySQL(dict_code);
				for(Map r:result){
					String value = String.valueOf(r.get(bean.get(CgReportConstant.ITEM_FIELDNAME)));
					for(Map m:dicDatas){
						String typecode = String.valueOf(m.get("typecode"));
						String typename = String.valueOf(m.get("typename"));
						if(value.equalsIgnoreCase(typecode)){
							r.put(bean.get(CgReportConstant.ITEM_FIELDNAME),typename);
						}
					}
				}
			}
		}
	}

	/**
	 * 查询数据字典，扩展了对SQL的支持
	 * @param dictCodeOrSQL 字典编码或SQL
	 * @author bit 2014-4-19
	 */
	private List<Map<String, Object>> queryDicBySQL(String dictCodeOrSQL) {
		List<Map<String, Object>> dicDatas = null;
		dictCodeOrSQL = dictCodeOrSQL.trim();
		if(dictCodeOrSQL.toLowerCase().startsWith("select ")) {

			dictCodeOrSQL = dictCodeOrSQL.replaceAll("'[kK][eE][yY]'", "typecode").replaceAll("'[vV][aA][lL][uU][eE]'", "typename");

			dicDatas = systemService.findForJdbc(dictCodeOrSQL, null);
		}else {
			dicDatas = queryDic(dictCodeOrSQL);
		}
		return dicDatas;
	}
	
	/**
	 * 处理取值表达式
	 * @param result
	 * @param beans
	 */
	@SuppressWarnings("unchecked")
	private void dealReplace(List<Map<String, Object>> result,
			List<Map<String,Object>> beans){
		for(Map<String,Object> bean:beans){
			try{
				//获取取值表达式
				String replace = (String) bean.get(CgReportConstant.ITEM_REPLACE);
				if(StringUtil.isEmpty(replace)){
					continue;
				}
				String[] groups = replace.split(",");
				for(String g:groups){
					String[] items = g.split("_");
					String v = items[0];//逻辑判断值
					String txt = items[1];//要转换的文本
					for(Map r:result){
						String value = String.valueOf(r.get(bean.get(CgReportConstant.ITEM_FIELDNAME)));
						if(value.equalsIgnoreCase(v)){
							r.put(bean.get(CgReportConstant.ITEM_FIELDNAME),txt);
						}
					}
				}
			}catch (Exception e) {
				//这里出现异常原因是因为取值表达式不正确
				logger.error("系统异常：",e);
				throw new BusinessException("取值表达式不正确");
			}
		}
	}
	/**
	 * 动态报表数据查询(不分页)
	 * @param configId 配置id-code
	 * @param request 
	 * @param response
	 */
	@SuppressWarnings("unchecked")
	@RequestMapping(params = "datagridGraph")
	public void datagridGraph(String configId,String page,String field,String rows, HttpServletRequest request,HttpServletResponse response) {
		//step.1 根据id获取该动态报表的配置参数
		Map<String, Object>  cgReportMap = null;
		try{
			cgReportMap = graphReportService.queryCgReportConfig(configId);//获取到sql语句，获取到item部分
			if(cgReportMap.size()<=0){
				throw new CgReportNotFoundException("动态报表配置不存在!");
			}
		}catch (Exception e) {
			logger.error("系统异常：",e);
			throw new CgReportNotFoundException("查找动态报表配置失败!"+e.getMessage());
		}
		PrintWriter writer = null;
				
		try {
			
			TSUser user = ResourceUtil.getSessionUserName(); // 取session用户
			String company=null;
			if(user.getUserKey().equals("省保险")){
				company=user.getRealName();
			}
			//step.2 获取该配置的查询SQL
			Map configM = (Map) cgReportMap.get(CgReportConstant.MAIN);//online配置页面的表头的所有数据
			String querySql = (String) configM.get("CGR_SQL");//获取sql语句
			List<Map<String,Object>> items = (List<Map<String, Object>>) cgReportMap.get(CgReportConstant.ITEMS);//获取item的值
			Map queryparams =  new LinkedHashMap<String,Object>();
			for(Map<String,Object> item:items){//循环报表参数列表
				String isQuery = (String) item.get(CgReportConstant.ITEM_ISQUERY);//寻找哪个字段需要查询
				if(CgReportConstant.BOOL_TRUE.equalsIgnoreCase(isQuery)){
					//step.3 装载查询条件
					ReportQueryParamUtil.loadQueryParams(request, item, queryparams);
					if(item.get(CgReportConstant.ITEM_FIELDTYPE).equals("Date") && item.get(CgReportConstant.ITEM_QUERYMODE).equals("single")){
						querySql=loadSql(request,querySql,item,company);
					}
				}
			}
			
			String area=request.getParameter("area");
			String depart=request.getParameter("depart");
			String sat=request.getParameter("sat");
			String spotCheck=request.getParameter("spotCheck");
			querySql=loadSqlOfDepartAndArea(querySql, depart, area,sat,spotCheck);
			
			String addTitle=ReportQueryParamUtil.getTimeForName(request, items,area,depart,company);//标题添加内容
			int p = page==null?1:Integer.parseInt(page);
			int r = rows==null?99999:Integer.parseInt(rows);
			//step.4 进行查询返回结果
			List<Map<String, Object>> result= graphReportService.queryByCgReportSql(querySql, queryparams, p, r);//拼装sql语句(把sql带{}的语句，还有要替换{}部分的语句拼接)，运行sql语句后返回的结果
			long size = countQueryByCgReportSql(querySql, queryparams);
			dealDic(result,items);//处理数据字典
			//dealReplace(result,items);//处理取值表达式
			
			
			//导出execel
			List<String> fields = new ArrayList<String>();
			List<Map<String, Object>> configItems = (List<Map<String, Object>>) cgReportMap.get(CgReportConstant.ITEMS);
			for (Map<String, Object> map : configItems) {
				if("Y".equals(map.get("is_show"))) {//确定那些要展示
					fields.add(map.get("field_txt").toString());
					fields.add(map.get("field_name").toString());
				}
			}
			
			
			if(exportExecel(request, response, configM.get("content").toString(), configM.get("content").toString(), fields.toArray(new String[fields.size()]), result, null,addTitle)) {
				return;
			}
			
			response.setContentType("application/json");
			response.setHeader("Cache-Control", "no-store");
			writer = response.getWriter();
			writer.println(ReportQueryParamUtil.getJson(result, size,addTitle));//将结果集转化为列表json格式，输出到页面
			writer.flush();
		} catch (IOException e) {
			e.printStackTrace();
			logger.error("系统异常：",e);
			throw new RuntimeException(e);
		}finally{
			try {
				if(writer!=null){
					writer.close();
				}
			} catch (Exception e2) {
				logger.error("系统异常：",e2);
				// TODO: handle exception
			}
		}
	}
	/**
	 * 动态报表数据查询
	 * @param configId 配置id-code
	 * @param page 分页页面
	 * @param rows 分页大小
	 * @param request 
	 * @param response
	 */
	/*@SuppressWarnings("unchecked")
	@RequestMapping(params = "datagrid")
	public void datagrid(String configId,String page,String field,String rows, HttpServletRequest request,
			HttpServletResponse response) {
		//step.1 根据id获取该动态报表的配置参数
		Map<String, Object>  cgReportMap = null;
		try{
			cgReportMap = graphReportService.queryCgReportConfig(configId);
			if(cgReportMap.size()<=0){
				throw new CgReportNotFoundException("动态报表配置不存在!");
			}
		}catch (Exception e) {
			throw new CgReportNotFoundException("查找动态报表配置失败!"+e.getMessage());
		}
		//step.2 获取该配置的查询SQL
		Map configM = (Map) cgReportMap.get(CgReportConstant.MAIN);
		String querySql = (String) configM.get(CgReportConstant.CONFIG_SQL);
		List<Map<String,Object>> items = (List<Map<String, Object>>) cgReportMap.get(CgReportConstant.ITEMS);
		Map queryparams =  new LinkedHashMap<String,Object>();
		for(Map<String,Object> item:items){
			String isQuery = (String) item.get(CgReportConstant.ITEM_ISQUERY);
			if(CgReportConstant.BOOL_TRUE.equalsIgnoreCase(isQuery)){
				//step.3 装载查询条件
				CgReportQueryParamUtil.loadQueryParams(request, item, queryparams);
			}
		}
		//step.4 进行查询返回结果
		int p = page==null?1:Integer.parseInt(page);
		int r = rows==null?99999:Integer.parseInt(rows);
		List<Map<String, Object>> result= graphReportService.queryByCgReportSql(querySql, queryparams, p, r);
		long size = graphReportService.countQueryByCgReportSql(querySql, queryparams);
		dealDic(result,items);
		dealReplace(result,items);
		response.setContentType("application/json");
		response.setHeader("Cache-Control", "no-store");
		PrintWriter writer = null;
		try {
			writer = response.getWriter();
			writer.println(CgReportQueryParamUtil.getJson(result,size));
			writer.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				writer.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	}*/
	/**
	 * 解析SQL，返回字段集
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@RequestMapping(params = "getFields", method = RequestMethod.POST)
	@ResponseBody
	public Object getSqlFields(String sql){
		List<String> result = null;
		Map reJson = new HashMap<String, Object>();
		try{
			result = graphReportService.getSqlFields(sql);
		}catch (Exception e) {
			e.printStackTrace();
			String errorInfo = "解析失败!<br><br>失败原因：";
			errorInfo += e.getMessage();
			reJson.put("status", "error");
			reJson.put("datas", errorInfo);
			return reJson;
		}
		reJson.put("status", "success");
		reJson.put("datas", result);
		return reJson;
	}
	/**
	 * 装载数据字典
	 * @param m	要放入freemarker的数据
	 * @param bean 读取出来的动态配置数据
	 */
	@SuppressWarnings("unchecked")
	private void loadDic(Map m, Map<String, Object> cgReportMap) {
		String dict_code = (String) cgReportMap.get("dict_code");
		if(StringUtil.isEmpty(dict_code)){
			m.put(CgReportConstant.FIELD_DICTLIST, new ArrayList(0));
			return;
		}
		List<Map<String, Object>> dicDatas = queryDicBySQL(dict_code);
		m.put(CgReportConstant.FIELD_DICTLIST, dicDatas);
	}
	/**
	 * 查询数据字典
	 * @param diccode 字典编码
	 * @return
	 */
	private List<Map<String, Object>> queryDic(String diccode) {
		StringBuilder dicSql = new StringBuilder();
		dicSql.append(" SELECT TYPECODE,TYPENAME FROM");
		dicSql.append(" "+CgReportConstant.SYS_DIC);
		dicSql.append(" "+"WHERE TYPEGROUPID = ");
		dicSql.append(" "+"(SELECT ID FROM "+CgReportConstant.SYS_DICGROUP+" WHERE TYPEGROUPCODE = '"+diccode+"' )");
		List<Map<String, Object>> dicDatas = graphReportService.findForJdbc(dicSql.toString());
		return dicDatas;
	}
	
	/**
	 * 导出execel
	 */
	private boolean exportExecel(HttpServletRequest request, HttpServletResponse response, 
			String title, String tagName, String[] fields, List<Map<String, Object>> list, Map<String, Object> params,String addTitle) {
		if(!"1".equals(request.getParameter("export"))) {
			return false;
		}
		
		if(params == null) {
			params = new HashMap<String, Object>();
		}
		if(StringUtil.isEmpty(tagName)) {
			tagName = title;
		}
		
		response.setContentType("application/vnd.ms-excel");
		OutputStream fOut = null;
		try {
			
			//step.4 根据浏览器进行转码，使其支持中文文件名
			String browse = BrowserUtils.checkBrowse(request);
			if ("MSIE".equalsIgnoreCase(browse.substring(0, 4))) {
				response.setHeader("content-disposition",
						"attachment;filename=" + java.net.URLEncoder.encode(title, "UTF-8") + ".xls");
			} else {
				String newtitle = new String(title.getBytes("UTF-8"), "ISO8859-1");
				addTitle=new String(addTitle.getBytes("UTF-8"), "ISO8859-1");
				response.setHeader("content-disposition",
						"attachment;filename=" +addTitle+ newtitle + ".xls");//设置标题
			}
			//step.5 产生工作簿对象
			HSSFWorkbook workbook = null;
			
			//让excel知道那些字段要显示
			List<Map<String, Object>> fieldList = new ArrayList<Map<String, Object>>();
			for (int i = 0; i < fields.length; i++,i++) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("field_txt", fields[i]);
				map.put("field_name", fields[i + 1]);
				fieldList.add(map);
			}
			
			workbook = cgReportExcelService.exportExcel(tagName, fieldList, list);
			fOut = response.getOutputStream();
			workbook.write(fOut);
			//TODO 增加操作日志
			//systemService.addLog(MsgUtil.getOperationLogMsg("导出成功", title, params), Globals.Log_Type_EXPORT,
			//		Globals.Log_Leavel_INFO);
		} catch (UnsupportedEncodingException e1) {
			logger.error("系统异常：",e1);
		} catch (Exception e) {
			logger.error("系统异常：",e);
		} finally {
			try {
				fOut.flush();
				if(fOut!=null){
					fOut.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return true;
	}
	/*
	 * 组装功能需要的sql语句
	 * */
	public  String loadSql(HttpServletRequest request, String sql, Map item,String company){
		Pattern p = Pattern.compile("\\$[^}]+\\$");//
		Matcher m = p.matcher(sql);
		Pattern a = Pattern.compile("\\#[^}]+\\#");
		Matcher b = a.matcher(sql);
		
		Pattern w = Pattern.compile("\\*[^}]+\\*");
		Matcher ww = w.matcher(sql);
		
		//如果条件中存在此标签则替换
		if(m.find()) {
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String filedName = (String) item.get(CgReportConstant.ITEM_FIELDNAME);//名称
			String month=request.getParameter(filedName.toLowerCase()+"_month");
			if(StringUtil.isEmpty(month)){
				SimpleDateFormat sd=new SimpleDateFormat("yyyy-MM");
				Date date=new Date();
				month=sd.format(date);
			}
			Date beginDay=new Date();
			Date endDay=new Date();
			
			String begin = month+"-01";
			String end = month+"-"+ReportQueryParamUtil.selectDate(month);
			
			 Calendar c1 = Calendar.getInstance();
		     Calendar c2 = Calendar.getInstance();
		     StringBuffer sb=new StringBuffer();
		     StringBuffer sb1=new StringBuffer();
		     StringBuffer sb3=new StringBuffer();
			try {
				if(begin!=null && !begin.equals("")){
					beginDay=sdf.parse(begin);
				}
				if(end!=null && !end.equals("")){
					endDay=sdf.parse(end);
				}
				c2.setTime(endDay);
				c2.add(Calendar.DATE, 1);//加一天
				endDay=c2.getTime();
				int d=(int) ((endDay.getTime()-beginDay.getTime())/(24*60*60*1000)); //相差天数
				for(int i=1;i<=d;i++){
					
					sb.append("tt."+"\""+i+"日\",");
					sb1.append("\""+i+"日\"+");
					c1.setTime(beginDay);
					c1.add(Calendar.DATE, i-1);
					sb3.append("sum(case datetime when '"+sdf.format(c1.getTime())+"' then tmp1.num else 0 end) as "+"\""+i+"日\",");
				}
				
				sql=sql.replace("$A$", sb.toString());
				sql=sql.replace("$B$", sb1.toString().subSequence(0, sb1.toString().length()-1));
				sql=sql.replace("$C$", sb3.toString().subSequence(0, sb3.toString().length()-1));
			} catch (ParseException e) {
				logger.error("系统异常：",e);
			}
		}
		if(b.find()){
			 StringBuffer sb4=new StringBuffer();
			 sb4.append("'"+company+"'");
			 sql=sql.replace("#name#", sb4.toString());
		}
		if(ww.find()){//排班表
			SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM-dd");
			String filedName1 = (String) item.get(CgReportConstant.ITEM_FIELDNAME);//名称
			String month1=request.getParameter(filedName1.toLowerCase()+"_month");
			if(StringUtil.isEmpty(month1)){
				SimpleDateFormat sd=new SimpleDateFormat("yyyy-MM");
				Date date=new Date();
				month1=sd.format(date);
			}
			Date beginDay1=new Date();
			Date endDay1=new Date();
			
			String begin1 = month1+"-01";
			String end1 = month1+"-"+ReportQueryParamUtil.selectDate(month1);
			
			 Calendar c11 = Calendar.getInstance();
		     Calendar c21 = Calendar.getInstance();
		     
		     StringBuffer sb31=new StringBuffer();
			try {
				if(begin1!=null && !begin1.equals("")){
					beginDay1=sdf1.parse(begin1);
				}
				if(end1!=null && !end1.equals("")){
					endDay1=sdf1.parse(end1);
				}
				c21.setTime(endDay1);
				c21.add(Calendar.DATE, 1);//加一天
				endDay1=c21.getTime();
				int d1=(int) ((endDay1.getTime()-beginDay1.getTime())/(24*60*60*1000)); //相差天数
				for(int i=1;i<=d1;i++){
					
					c11.setTime(beginDay1);
					c11.add(Calendar.DATE, i-1);
					sb31.append("max (case datetime when '"+sdf1.format(c11.getTime())+"' then case TMP1.STATE WHEN '3' THEN '替班' ELSE case tmp1.num when 'l' then '迟到' "+
							" when 'oc' then '上班缺卡' "+
							" when 'fc' then '下班缺卡' "+
							"when 'f' then '旷工' "+
							"when 'c' then '替班' "+
							"when 'e' then '早退' "+
							"when 'el' then '迟到与早退' "+
							"  when 't' then '正常' end end end)as "+"\""+i+"日\",");
				}
				sql=sql.replace("*w*", sb31.toString());
			} catch (ParseException e) {
				logger.error("系统异常：",e);
			}
		}
		return sql;
	}
	
	
	public long countQueryByCgReportSql(String sql, Map params) {
		sql = graphReportService.handleElInSQL(sql, params);
		
		sql = "SELECT COUNT(pernme) FROM ("+sql+") t4";
		long result = jdbcDao.findForLong(sql,new HashMap(0));//框架原本的
		return result;
	}
	/*组装地市和区域的sql*/
	public  String loadSqlOfDepartAndArea(String sql,String depart,String area,String sat,String spotCheck){
		Pattern a = Pattern.compile("\\#[^}]+\\#");
		Matcher b = a.matcher(sql);
		
		if(b.find() && StringUtil.isNotEmpty(depart) && !depart.equals("0")){
			 
			 sql=sql.replace("#depart#", "t.T_DPT_CODE='"+depart+"'");
			 sql=sql.replace("#appdepart#", "TAU.T_DPT_CODE='"+depart+"'");
			 sql=sql.replace("#policeCity#", "p.CITY='"+depart+"'");//交警
		}else{
			sql=sql.replace("#depart#", "1=1");
			sql=sql.replace("#appdepart#", "1=1");
			sql=sql.replace("#policeCity#", "1=1");//交警
		}
		Matcher d = a.matcher(sql);
		if(d.find()&& StringUtil.isNotEmpty(area) && !area.equals("0")){
			
			 sql=sql.replace("#area#", "TAU.T_AREA='"+area+"'");
			 sql=sql.replace("#policeArea#", "p.AREA='"+area+"'");//交警
			 
		}else{
			sql=sql.replace("#area#", "1=1");
			sql=sql.replace("#policeArea#", "1=1");//交警
		}
		//服务点的表，因为用到left join 所以把左表的信息查出来，比较麻烦
		Matcher  c= a.matcher(sql);
		if(c.find() && StringUtil.isNotEmpty(depart) && !depart.equals("0")){
			if(depart.equals("A04A01")){
				if(StringUtil.isNotEmpty(area) && area.equals("2")){
					sql=sql.replace("#code#", "code='GZGS'");
				}else{
					sql=sql.replace("#code#", "code='GZ'");
				}
			}else if(depart.equals("A04A02")){
				if(StringUtil.isNotEmpty(area) && area.equals("2")){
					sql=sql.replace("#code#", "code='FSGS'");
				}else{
					sql=sql.replace("#code#", "code='FS'");
				}
				
			}else{
				sql=sql.replace("#code#", "1=1");
			}
		}else{
			sql=sql.replace("#code#", "1=1");
		}
		
		//满意度明细表
		Matcher  m= a.matcher(sql);
		if(m.find() && StringUtil.isNotEmpty(sat)){
			if(sat.equals("1")){
				sql=sql.replace("#SATISFACTION#", "TSS.T_SATISFACTION='1'");
			}else if(sat.equals("2")){
				sql=sql.replace("#SATISFACTION#", "TSS.T_SATISFACTION='2'");
			}else if(sat.equals("0")){
				sql=sql.replace("#SATISFACTION#", "TSS.T_SATISFACTION='0'");
			}else if(sat.equals("no")){
				sql=sql.replace("#SATISFACTION#", "TSS.T_TIME_OUT='y'");
			}else {
				sql=sql.replace("#SATISFACTION#", "1=1");
			}
			
		}else {
			sql=sql.replace("#SATISFACTION#", "1=1");
		}
		
		Matcher  y= a.matcher(sql);
		if(y.find() && StringUtil.isNotEmpty(spotCheck)){
			if(spotCheck.equals("p")){
				sql=sql.replace("#CHECK#", "T.T_SPOTCHECK='p'");
			}else if(spotCheck.equals("f")){
				sql=sql.replace("#CHECK#", "T.T_SPOTCHECK='f'");
			}else if(spotCheck.equals("n")){
				sql=sql.replace("#CHECK#", "T.T_SPOTCHECK='n'");
			}else {
				sql=sql.replace("#CHECK#", "1=1");
			}
			
		}else {
			sql=sql.replace("#CHECK#", "1=1");
		}
		
		return sql;
	}
	
}
